﻿namespace WMS6.SQLServerDAL
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;
    using WMS6.Common;
    using WMS6.DataEntity;
    using WMS6.DBUtility;
    using WMS6.IDAL;
    using WMS6.QueryEntity;

    public class BillingEntityDA : IBillingEntityDA
    {
        private string ADDRESS = "@ADDRESS";
        private string B_ADDRESS = "@B_ADDRESS";
        private string B_CITY = "@B_CITY";
        private string B_CONTACT1 = "@B_CONTACT1";
        private string B_CONTACT2 = "@B_CONTACT2";
        private string B_COUNTRY = "@B_COUNTRY";
        private string B_COUNTRY_CODE = "@B_COUNTRY_CODE";
        private string B_EMAIL1 = "@B_EMAIL1";
        private string B_EMAIL2 = "@B_EMAIL2";
        private string B_FAX1 = "@B_FAX1";
        private string B_FAX2 = "@B_FAX2";
        private string B_PHONE1 = "@B_PHONE1";
        private string B_PHONE2 = "@B_PHONE2";
        private string B_STATE = "@B_STATE";
        private string B_ZIP = "@B_ZIP";
        private string BILLING_ENTITY_ID = "@BILLING_ENTITY_ID";
        private string CITY = "@CITY";
        private string COMPANY = "@COMPANY";
        private string CONTACT1 = "@CONTACT1";
        private string CONTACT2 = "@CONTACT2";
        private string COUNTRY = "@COUNTRY";
        private string COUNTRY_CODE = "@COUNTRY_CODE";
        private string CREATED_BY = "@CREATED_BY";
        private string DESCRIPTION = "@DESCRIPTION";
        private string EMAIL1 = "@EMAIL1";
        private string EMAIL2 = "@EMAIL2";
        private string FAX1 = "@FAX1";
        private string FAX2 = "@FAX2";
        private string IS_ACTIVE = "@IS_ACTIVE";
        private string PHONE1 = "@PHONE1";
        private string PHONE2 = "@PHONE2";
        private string REMARK = "@REMARK";
        private string SQL_CHECK_AUTHORIZATION_BILLINGENTITY_USERID = "SELECT COUNT(1) FROM @_@BILLING_ENTITY B INNER JOIN SYSADM.USER_BILLINGENTITY UB ON B.BILLING_ENTITY_ID = UB.BILLINGENTITY_ID AND UB.USER_ID=@USER_ID WHERE B.BILLING_ENTITY_ID = @BILLING_ENTITY_ID";
        private string SQL_CHECK_BILLING_ENTITY_ID_UNIQUE = " SELECT COUNT(1) FROM @_@BILLING_ENTITY WHERE BILLING_ENTITY_ID = @BILLING_ENTITY_ID ";
        private string SQL_DELETE_BILLING_ENTITY = " DELETE FROM @_@BILLING_ENTITY WHERE BILLING_ENTITY_ID = @BILLING_ENTITY_ID ";
        private string SQL_INSERT_BILLING_ENTITY = " INSERT INTO @_@BILLING_ENTITY ( WH_ID, BILLING_ENTITY_ID, COMPANY, DESCRIPTION, VAT, CITY, STATE, ZIP, COUNTRY, COUNTRY_CODE, ADDRESS, CONTACT1, CONTACT2, PHONE1, PHONE2, FAX1, FAX2, EMAIL1, EMAIL2, B_CITY, B_STATE, B_ZIP, B_COUNTRY, B_COUNTRY_CODE, B_ADDRESS, B_CONTACT1, B_CONTACT2, B_PHONE1, B_PHONE2, B_FAX1, B_FAX2, B_EMAIL1, B_EMAIL2, UDF1, UDF2, UDF3, UDF4, UDF5, UDF6, UDF7, UDF8, UDF9, UDF10, REMARK, IS_ACTIVE, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE) VALUES ( @WH_ID, @BILLING_ENTITY_ID, @COMPANY, @DESCRIPTION, @VAT, @CITY, @STATE, @ZIP, @COUNTRY, @COUNTRY_CODE, @ADDRESS, @CONTACT1, @CONTACT2, @PHONE1, @PHONE2, @FAX1, @FAX2, @EMAIL1, @EMAIL2, @B_CITY, @B_STATE, @B_ZIP, @B_COUNTRY, @B_COUNTRY_CODE, @B_ADDRESS, @B_CONTACT1, @B_CONTACT2, @B_PHONE1, @B_PHONE2, @B_FAX1, @B_FAX2, @B_EMAIL1, @B_EMAIL2, @UDF1, @UDF2, @UDF3, @UDF4, @UDF5, @UDF6, @UDF7, @UDF8, @UDF9, @UDF10, @REMARK, @IS_ACTIVE, @CREATED_BY, GETDATE(), @UPDATED_BY, GETDATE() )  ";
        private string SQL_SELECT_ALL_BILLING_ENTITY = " SELECT  B.ROWID, B.WH_ID, B.BILLING_ENTITY_ID, B.COMPANY, B.DESCRIPTION, B.VAT, B.CITY, B.STATE, B.ZIP, B.COUNTRY, B.COUNTRY_CODE, B.ADDRESS, B.CONTACT1, B.CONTACT2, B.PHONE1, B.PHONE2, B.FAX1, B.FAX2, B.EMAIL1, B.EMAIL2, B.B_CITY, B.B_STATE, B.B_ZIP, B.B_COUNTRY, B.B_COUNTRY_CODE, B.B_ADDRESS, B.B_CONTACT1, B.B_CONTACT2, B.B_PHONE1, B.B_PHONE2, B.B_FAX1, B.B_FAX2, B.B_EMAIL1, B.B_EMAIL2, B.UDF1, B.UDF2, B.UDF3, B.UDF4, B.UDF5, B.UDF6, B.UDF7, B.UDF8, B.UDF9, B.UDF10, B.REMARK, B.IS_ACTIVE, B.TS, B.CREATED_BY, B.CREATED_DATE, B.UPDATED_BY, B.UPDATED_DATE  FROM  @_@BILLING_ENTITY B INNER JOIN SYSADM.USER_BILLINGENTITY UB ON B.BILLING_ENTITY_ID = UB.BILLINGENTITY_ID AND UB.[USER_ID] = @USER_ID ";
        private string SQL_UPDATE_BILLING_ENTITY = " UPDATE @_@BILLING_ENTITY SET WH_ID = @WH_ID, COMPANY = @COMPANY, DESCRIPTION = @DESCRIPTION, VAT = @VAT, CITY = @CITY, STATE = @STATE, ZIP = @ZIP, COUNTRY = @COUNTRY, COUNTRY_CODE = @COUNTRY_CODE, ADDRESS = @ADDRESS, CONTACT1 = @CONTACT1, CONTACT2 = @CONTACT2, PHONE1 = @PHONE1, PHONE2 = @PHONE2, FAX1 = @FAX1, FAX2 = @FAX2, EMAIL1 = @EMAIL1, EMAIL2 = @EMAIL2, B_CITY = @B_CITY, B_STATE = @B_STATE, B_ZIP = @B_ZIP, B_COUNTRY = @B_COUNTRY, B_COUNTRY_CODE = @B_COUNTRY_CODE, B_ADDRESS = @B_ADDRESS, B_CONTACT1 = @B_CONTACT1, B_CONTACT2 = @B_CONTACT2, B_PHONE1 = @B_PHONE1, B_PHONE2 = @B_PHONE2, B_FAX1 = @B_FAX1, B_FAX2 = @B_FAX2, B_EMAIL1 = @B_EMAIL1, B_EMAIL2 = @B_EMAIL2, UDF1 = @UDF1, UDF2 = @UDF2, UDF3 = @UDF3, UDF4 = @UDF4, UDF5 = @UDF5, UDF6 = @UDF6, UDF7 = @UDF7, UDF8 = @UDF8, UDF9 = @UDF9, UDF10 = @UDF10, REMARK = @REMARK, IS_ACTIVE = @IS_ACTIVE, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE BILLING_ENTITY_ID = @BILLING_ENTITY_ID  ";
        private string STATE = "@STATE";
        private string TS = "@TS";
        private string UDF1 = "@UDF1";
        private string UDF10 = "@UDF10";
        private string UDF2 = "@UDF2";
        private string UDF3 = "@UDF3";
        private string UDF4 = "@UDF4";
        private string UDF5 = "@UDF5";
        private string UDF6 = "@UDF6";
        private string UDF7 = "@UDF7";
        private string UDF8 = "@UDF8";
        private string UDF9 = "@UDF9";
        private string UPDATED_BY = "@UPDATED_BY";
        private string VAT = "@VAT";
        private string WH_ID = "@WH_ID";
        private string ZIP = "@ZIP";

        public bool CheckAuthorizationBillingEntityUserID(string billingEntityID, PubParasInfo pubParasInfo, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@BILLING_ENTITY_ID", billingEntityID), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            return (DBHelper.ExecuteScalar(CommandType.Text, this.SQL_CHECK_AUTHORIZATION_BILLINGENTITY_USERID.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public bool CheckAuthorizationBillingEntityUserID(DataBase dataBase, DbTransaction tran, string billingEntityID, PubParasInfo pubParasInfo, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@BILLING_ENTITY_ID", billingEntityID), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            return (dataBase.ExecuteScalar(tran, CommandType.Text, this.SQL_CHECK_AUTHORIZATION_BILLINGENTITY_USERID.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public bool CheckBillingEntityIDUnique(string billingEntityID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@BILLING_ENTITY_ID", billingEntityID) };
            return (DBHelper.ExecuteScalar(CommandType.Text, this.SQL_CHECK_BILLING_ENTITY_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public bool CheckBillingEntityIDUnique(DataBase dataBase, DbTransaction tran, string billingEntityID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@BILLING_ENTITY_ID", billingEntityID) };
            return (dataBase.ExecuteScalar(tran, CommandType.Text, this.SQL_CHECK_BILLING_ENTITY_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public int DeleteBillingEntity(List<string> billingEntityID, List<string> whLoginID)
        {
            int result = 0;
            DataBase dataBase = new DataBase();
            DbConnection conn = dataBase.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    for (int j = 0; j < billingEntityID.Count; j++)
                    {
                        SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@BILLING_ENTITY_ID", billingEntityID[j]) };
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_BILLING_ENTITY.Replace("@_@", whLoginID[i]), paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int DeleteBillingEntity(DataBase dataBase, DbTransaction tran, List<string> billingEntityID, List<string> whLoginID)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    for (int j = 0; j < billingEntityID.Count; j++)
                    {
                        SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@BILLING_ENTITY_ID", billingEntityID[j]) };
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_BILLING_ENTITY.Replace("@_@", whLoginID[i]), paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public DataSet GetAllBillingEntity(string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_BILLING_ENTITY.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            return DBHelper.ExecuteDataSet(CommandType.Text, sql, paras);
        }

        public DataSet GetAllBillingEntity(DataBase dataBase, DbTransaction tran, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_BILLING_ENTITY.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            return dataBase.ExecuteDataSet(tran, CommandType.Text, sql, paras);
        }

        public BillingEntityInfo GetBillingEntityByID(string billingentityID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_BILLING_ENTITY.Replace("@_@", whLoginID) + " WHERE B.BILLING_ENTITY_ID = @BILLING_ENTITY_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@BILLING_ENTITY_ID", billingentityID), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            BillingEntityInfo billingEntityInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                if (reader.Read())
                {
                    billingEntityInfo = new BillingEntityInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["BILLING_ENTITY_ID"].ToString(), reader["COMPANY"].ToString(), reader["DESCRIPTION"].ToString(), reader["VAT"].ToString(), reader["CITY"].ToString(), reader["STATE"].ToString(), reader["ZIP"].ToString(), reader["COUNTRY"].ToString(), reader["COUNTRY_CODE"].ToString(), reader["ADDRESS"].ToString(), reader["CONTACT1"].ToString(), reader["CONTACT2"].ToString(), reader["PHONE1"].ToString(), reader["PHONE2"].ToString(), reader["FAX1"].ToString(), reader["FAX2"].ToString(), reader["EMAIL1"].ToString(), reader["EMAIL2"].ToString(), reader["B_CITY"].ToString(), reader["B_STATE"].ToString(), reader["B_ZIP"].ToString(), reader["B_COUNTRY"].ToString(), reader["B_COUNTRY_CODE"].ToString(), reader["B_ADDRESS"].ToString(), reader["B_CONTACT1"].ToString(), reader["B_CONTACT2"].ToString(), reader["B_PHONE1"].ToString(), reader["B_PHONE2"].ToString(), reader["B_FAX1"].ToString(), reader["B_FAX2"].ToString(), reader["B_EMAIL1"].ToString(), reader["B_EMAIL2"].ToString(), reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REMARK"].ToString(), reader["IS_ACTIVE"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : new DateTime(), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime());
                }
            }
            return billingEntityInfo;
        }

        public BillingEntityInfo GetBillingEntityByID(DataBase dataBase, DbTransaction tran, string billingentityID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_BILLING_ENTITY.Replace("@_@", whLoginID) + " WHERE B.BILLING_ENTITY_ID = @BILLING_ENTITY_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@BILLING_ENTITY_ID", billingentityID), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            BillingEntityInfo billingEntityInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            if (reader.Read())
            {
                billingEntityInfo = new BillingEntityInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["BILLING_ENTITY_ID"].ToString(), reader["COMPANY"].ToString(), reader["DESCRIPTION"].ToString(), reader["VAT"].ToString(), reader["CITY"].ToString(), reader["STATE"].ToString(), reader["ZIP"].ToString(), reader["COUNTRY"].ToString(), reader["COUNTRY_CODE"].ToString(), reader["ADDRESS"].ToString(), reader["CONTACT1"].ToString(), reader["CONTACT2"].ToString(), reader["PHONE1"].ToString(), reader["PHONE2"].ToString(), reader["FAX1"].ToString(), reader["FAX2"].ToString(), reader["EMAIL1"].ToString(), reader["EMAIL2"].ToString(), reader["B_CITY"].ToString(), reader["B_STATE"].ToString(), reader["B_ZIP"].ToString(), reader["B_COUNTRY"].ToString(), reader["B_COUNTRY_CODE"].ToString(), reader["B_ADDRESS"].ToString(), reader["B_CONTACT1"].ToString(), reader["B_CONTACT2"].ToString(), reader["B_PHONE1"].ToString(), reader["B_PHONE2"].ToString(), reader["B_FAX1"].ToString(), reader["B_FAX2"].ToString(), reader["B_EMAIL1"].ToString(), reader["B_EMAIL2"].ToString(), reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REMARK"].ToString(), reader["IS_ACTIVE"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : new DateTime(), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime());
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return billingEntityInfo;
        }

        public DataSet GetBillingEntityByQueryList(List<string> sqlWhere, BillingEntityQueryEntity billingentityQuery, string whLoginID, PubParasInfo pubParasInfo)
        {
            string temp = this.SQL_SELECT_ALL_BILLING_ENTITY.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!billingentityQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, billingentityQuery.CurrentPage, billingentityQuery.PageSize, billingentityQuery.SortField, billingentityQuery.SortDirection).Replace("@USER_ID", "''" + pubParasInfo.UserID + "''");
            }
            else
            {
                temp = temp.Replace("@USER_ID", "'" + pubParasInfo.UserID + "'");
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, temp);
        }

        public DataSet GetBillingEntityByQueryList(DataBase dataBase, DbTransaction tran, List<string> sqlWhere, BillingEntityQueryEntity billingentityQuery, string whLoginID, PubParasInfo pubParasInfo)
        {
            string temp = this.SQL_SELECT_ALL_BILLING_ENTITY.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!billingentityQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, billingentityQuery.CurrentPage, billingentityQuery.PageSize, billingentityQuery.SortField, billingentityQuery.SortDirection).Replace("@USER_ID", "''" + pubParasInfo.UserID + "''");
            }
            else
            {
                temp = temp.Replace("@USER_ID", "'" + pubParasInfo.UserID + "'");
            }
            return dataBase.ExecuteDataSet(tran, CommandType.Text, temp);
        }

        public int InsertBillingEntity(BillingEntityInfo billingEntityInfo, List<string> whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    billingEntityInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_BillingEntity_Parameters(billingEntityInfo);
                    if (paras != null)
                    {
                        result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_BILLING_ENTITY.Replace("@_@", whLoginID[i]), paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int InsertBillingEntity(DataBase dataBase, DbTransaction tran, BillingEntityInfo billingEntityInfo, List<string> whLoginID)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    billingEntityInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_BillingEntity_Parameters(billingEntityInfo);
                    if (paras != null)
                    {
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_BILLING_ENTITY.Replace("@_@", whLoginID[i]), paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        private SqlParameter[] Set_BillingEntity_Parameters(BillingEntityInfo billingEntityInfo)
        {
            SqlParameter[] paramArray = new SqlParameter[] { 
                new SqlParameter(this.WH_ID, SqlDbType.VarChar, 30), new SqlParameter(this.BILLING_ENTITY_ID, SqlDbType.VarChar, 20), new SqlParameter(this.COMPANY, SqlDbType.VarChar, 50), new SqlParameter(this.DESCRIPTION, SqlDbType.VarChar, 50), new SqlParameter(this.VAT, SqlDbType.VarChar, 20), new SqlParameter(this.CITY, SqlDbType.VarChar, 50), new SqlParameter(this.STATE, SqlDbType.VarChar, 20), new SqlParameter(this.ZIP, SqlDbType.VarChar, 20), new SqlParameter(this.COUNTRY, SqlDbType.VarChar, 30), new SqlParameter(this.COUNTRY_CODE, SqlDbType.VarChar, 20), new SqlParameter(this.ADDRESS, SqlDbType.VarChar, 200), new SqlParameter(this.CONTACT1, SqlDbType.VarChar, 30), new SqlParameter(this.CONTACT2, SqlDbType.VarChar, 30), new SqlParameter(this.PHONE1, SqlDbType.VarChar, 20), new SqlParameter(this.PHONE2, SqlDbType.VarChar, 20), new SqlParameter(this.FAX1, SqlDbType.VarChar, 20), 
                new SqlParameter(this.FAX2, SqlDbType.VarChar, 20), new SqlParameter(this.EMAIL1, SqlDbType.VarChar, 60), new SqlParameter(this.EMAIL2, SqlDbType.VarChar, 60), new SqlParameter(this.B_CITY, SqlDbType.VarChar, 50), new SqlParameter(this.B_STATE, SqlDbType.VarChar, 20), new SqlParameter(this.B_ZIP, SqlDbType.VarChar, 20), new SqlParameter(this.B_COUNTRY, SqlDbType.VarChar, 30), new SqlParameter(this.B_COUNTRY_CODE, SqlDbType.VarChar, 20), new SqlParameter(this.B_ADDRESS, SqlDbType.VarChar, 200), new SqlParameter(this.B_CONTACT1, SqlDbType.VarChar, 30), new SqlParameter(this.B_CONTACT2, SqlDbType.VarChar, 30), new SqlParameter(this.B_PHONE1, SqlDbType.VarChar, 20), new SqlParameter(this.B_PHONE2, SqlDbType.VarChar, 20), new SqlParameter(this.B_FAX1, SqlDbType.VarChar, 20), new SqlParameter(this.B_FAX2, SqlDbType.VarChar, 20), new SqlParameter(this.B_EMAIL1, SqlDbType.VarChar, 60), 
                new SqlParameter(this.B_EMAIL2, SqlDbType.VarChar, 60), new SqlParameter(this.UDF1, SqlDbType.VarChar, 30), new SqlParameter(this.UDF2, SqlDbType.VarChar, 30), new SqlParameter(this.UDF3, SqlDbType.VarChar, 30), new SqlParameter(this.UDF4, SqlDbType.VarChar, 30), new SqlParameter(this.UDF5, SqlDbType.VarChar, 30), new SqlParameter(this.UDF6, SqlDbType.VarChar, 30), new SqlParameter(this.UDF7, SqlDbType.VarChar, 30), new SqlParameter(this.UDF8, SqlDbType.VarChar, 30), new SqlParameter(this.UDF9, SqlDbType.VarChar, 30), new SqlParameter(this.UDF10, SqlDbType.VarChar, 30), new SqlParameter(this.REMARK, SqlDbType.VarChar, 0x7d0), new SqlParameter(this.IS_ACTIVE, SqlDbType.VarChar, 1), new SqlParameter(this.CREATED_BY, SqlDbType.VarChar, 20), new SqlParameter(this.UPDATED_BY, SqlDbType.VarChar, 20)
             };
            if (!string.IsNullOrEmpty(billingEntityInfo.WhID))
            {
                paramArray[0].Value = billingEntityInfo.WhID;
            }
            else
            {
                paramArray[0].Value = DBNull.Value;
            }
            paramArray[1].Value = billingEntityInfo.BillingEntityID;
            if (!string.IsNullOrEmpty(billingEntityInfo.Company))
            {
                paramArray[2].Value = billingEntityInfo.Company;
            }
            else
            {
                paramArray[2].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(billingEntityInfo.Description))
            {
                paramArray[3].Value = billingEntityInfo.Description;
            }
            else
            {
                paramArray[3].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(billingEntityInfo.Vat))
            {
                paramArray[4].Value = billingEntityInfo.Vat;
            }
            else
            {
                paramArray[4].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(billingEntityInfo.City))
            {
                paramArray[5].Value = billingEntityInfo.City;
            }
            else
            {
                paramArray[5].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(billingEntityInfo.State))
            {
                paramArray[6].Value = billingEntityInfo.State;
            }
            else
            {
                paramArray[6].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(billingEntityInfo.Zip))
            {
                paramArray[7].Value = billingEntityInfo.Zip;
            }
            else
            {
                paramArray[7].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(billingEntityInfo.Country))
            {
                paramArray[8].Value = billingEntityInfo.Country;
            }
            else
            {
                paramArray[8].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(billingEntityInfo.CountryCode))
            {
                paramArray[9].Value = billingEntityInfo.CountryCode;
            }
            else
            {
                paramArray[9].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(billingEntityInfo.Address))
            {
                paramArray[10].Value = billingEntityInfo.Address;
            }
            else
            {
                paramArray[10].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(billingEntityInfo.Contact1))
            {
                paramArray[11].Value = billingEntityInfo.Contact1;
            }
            else
            {
                paramArray[11].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(billingEntityInfo.Contact2))
            {
                paramArray[12].Value = billingEntityInfo.Contact2;
            }
            else
            {
                paramArray[12].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(billingEntityInfo.Phone1))
            {
                paramArray[13].Value = billingEntityInfo.Phone1;
            }
            else
            {
                paramArray[13].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(billingEntityInfo.Phone2))
            {
                paramArray[14].Value = billingEntityInfo.Phone2;
            }
            else
            {
                paramArray[14].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(billingEntityInfo.Fax1))
            {
                paramArray[15].Value = billingEntityInfo.Fax1;
            }
            else
            {
                paramArray[15].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(billingEntityInfo.Fax2))
            {
                paramArray[0x10].Value = billingEntityInfo.Fax2;
            }
            else
            {
                paramArray[0x10].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(billingEntityInfo.Email1))
            {
                paramArray[0x11].Value = billingEntityInfo.Email1;
            }
            else
            {
                paramArray[0x11].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(billingEntityInfo.Email2))
            {
                paramArray[0x12].Value = billingEntityInfo.Email2;
            }
            else
            {
                paramArray[0x12].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(billingEntityInfo.BCity))
            {
                paramArray[0x13].Value = billingEntityInfo.BCity;
            }
            else
            {
                paramArray[0x13].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(billingEntityInfo.BState))
            {
                paramArray[20].Value = billingEntityInfo.BState;
            }
            else
            {
                paramArray[20].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(billingEntityInfo.BZip))
            {
                paramArray[0x15].Value = billingEntityInfo.BZip;
            }
            else
            {
                paramArray[0x15].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(billingEntityInfo.BCountry))
            {
                paramArray[0x16].Value = billingEntityInfo.BCountry;
            }
            else
            {
                paramArray[0x16].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(billingEntityInfo.BCountryCode))
            {
                paramArray[0x17].Value = billingEntityInfo.BCountryCode;
            }
            else
            {
                paramArray[0x17].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(billingEntityInfo.BAddress))
            {
                paramArray[0x18].Value = billingEntityInfo.BAddress;
            }
            else
            {
                paramArray[0x18].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(billingEntityInfo.BContact1))
            {
                paramArray[0x19].Value = billingEntityInfo.BContact1;
            }
            else
            {
                paramArray[0x19].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(billingEntityInfo.BContact2))
            {
                paramArray[0x1a].Value = billingEntityInfo.BContact2;
            }
            else
            {
                paramArray[0x1a].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(billingEntityInfo.BPhone1))
            {
                paramArray[0x1b].Value = billingEntityInfo.BPhone1;
            }
            else
            {
                paramArray[0x1b].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(billingEntityInfo.BPhone2))
            {
                paramArray[0x1c].Value = billingEntityInfo.BPhone2;
            }
            else
            {
                paramArray[0x1c].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(billingEntityInfo.BFax1))
            {
                paramArray[0x1d].Value = billingEntityInfo.BFax1;
            }
            else
            {
                paramArray[0x1d].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(billingEntityInfo.BFax2))
            {
                paramArray[30].Value = billingEntityInfo.BFax2;
            }
            else
            {
                paramArray[30].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(billingEntityInfo.BEmail1))
            {
                paramArray[0x1f].Value = billingEntityInfo.BEmail1;
            }
            else
            {
                paramArray[0x1f].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(billingEntityInfo.BEmail2))
            {
                paramArray[0x20].Value = billingEntityInfo.BEmail2;
            }
            else
            {
                paramArray[0x20].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(billingEntityInfo.Udf1))
            {
                paramArray[0x21].Value = billingEntityInfo.Udf1;
            }
            else
            {
                paramArray[0x21].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(billingEntityInfo.Udf2))
            {
                paramArray[0x22].Value = billingEntityInfo.Udf2;
            }
            else
            {
                paramArray[0x22].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(billingEntityInfo.Udf3))
            {
                paramArray[0x23].Value = billingEntityInfo.Udf3;
            }
            else
            {
                paramArray[0x23].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(billingEntityInfo.Udf4))
            {
                paramArray[0x24].Value = billingEntityInfo.Udf4;
            }
            else
            {
                paramArray[0x24].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(billingEntityInfo.Udf5))
            {
                paramArray[0x25].Value = billingEntityInfo.Udf5;
            }
            else
            {
                paramArray[0x25].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(billingEntityInfo.Udf6))
            {
                paramArray[0x26].Value = billingEntityInfo.Udf6;
            }
            else
            {
                paramArray[0x26].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(billingEntityInfo.Udf7))
            {
                paramArray[0x27].Value = billingEntityInfo.Udf7;
            }
            else
            {
                paramArray[0x27].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(billingEntityInfo.Udf8))
            {
                paramArray[40].Value = billingEntityInfo.Udf8;
            }
            else
            {
                paramArray[40].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(billingEntityInfo.Udf9))
            {
                paramArray[0x29].Value = billingEntityInfo.Udf9;
            }
            else
            {
                paramArray[0x29].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(billingEntityInfo.Udf10))
            {
                paramArray[0x2a].Value = billingEntityInfo.Udf10;
            }
            else
            {
                paramArray[0x2a].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(billingEntityInfo.Remark))
            {
                paramArray[0x2b].Value = billingEntityInfo.Remark;
            }
            else
            {
                paramArray[0x2b].Value = DBNull.Value;
            }
            paramArray[0x2c].Value = billingEntityInfo.IsActive;
            paramArray[0x2d].Value = billingEntityInfo.CreatedBy;
            paramArray[0x2e].Value = billingEntityInfo.UpdatedBy;
            return paramArray;
        }

        public int UpdateBillingEntity(BillingEntityInfo billingEntityInfo, List<string> whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    billingEntityInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_BillingEntity_Parameters(billingEntityInfo);
                    if (paras != null)
                    {
                        result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_BILLING_ENTITY.Replace("@_@", whLoginID[i]), paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int UpdateBillingEntity(DataBase dataBase, DbTransaction tran, BillingEntityInfo billingEntityInfo, List<string> whLoginID)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    billingEntityInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_BillingEntity_Parameters(billingEntityInfo);
                    if (paras != null)
                    {
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_BILLING_ENTITY.Replace("@_@", whLoginID[i]), paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }
    }
}

